﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EmplDept;

namespace SqlToModel
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("-------= LINQ to MODEL=-----");
            Linq1();
            Linq2();
            Linq3(3);
            Linq4();
            Linq5();
            Linq6();
            Linq7();
            Linq8();
            Linq9();
            Linq10();
            Console.ReadLine();
        }
               
        /// <summary>
        /// 1. Выведите список городов, где находятся подразделения компании.
        /// </summary>
        public static void Linq1()
        {
            var cities = from city in departments
                         select city.City;
            ///extensions
            /// var cities = departments.Select(city => city.City);

            Console.WriteLine("-1--------------Cities--------------");
            foreach (var item in cities)
            {
                Console.WriteLine(item);
            }
        }

        /// <summary>
        /// Выведите список имен работников (без повторов, упорядочен по алфавиту).
        /// </summary>
        public static void Linq2()
        {
            var names = (from name in employees
                         orderby name.FirstName
                         select name.FirstName).Distinct();

            ///extensions
            ///var names = employees.OrderBy(a => a.FirstName).Select(name => name.FirstName).Distinct();
            ///variant 2
            /*var namesGroups = from name in employees
                              orderby name.FirstName
                              group name by name.FirstName into nameGroup
                              select nameGroup;*/
            ///variant 2 extensions
            /// var namesGroups = employees.GroupBy(a => a.FirstName).OrderBy(a => a.Key);

            Console.WriteLine("-2--------------Names--------------");
            foreach (var item in names)
            {
                Console.WriteLine(item);
            }
        }

        /// <summary>
        /// Посчитайте, сколько работников у компании в заданном подразделении (номер).
        /// </summary>
        /// <param name="deptId">номер подразделения</param>
        public static void Linq3(int deptId)
        {
            /*var amount = (from name in employees
                         where name.DeptId==deptId
                         select name.FirstName).Count();*/

            var amount = employees.Where(employee => (employee.DeptId == deptId)).Select(name => name.FirstName).Count();

            Console.WriteLine("-3--------------All employees from 3d department--------------");
            Console.WriteLine(amount);
        }

        /// <summary>
        /// Получите список количества работников в каждом подразделении (номер). Упорядочить по убыванию числа работников.
        /// </summary>
        public static void Linq4()
        {
            /*var IDandAmount = from employee in employees
                              group employee by employee.DeptId into dept
                              orderby dept.Count() descending
                              select new { ID = dept.Key, Amount = dept.Count() };*/
            
            var IDandAmount = employees.Select(empl => empl).GroupBy(emp => emp.DeptId).OrderBy(e => e.Count()).Reverse().Select(item => new { ID = item.Key, Amount = item.Count() });

            Console.WriteLine("-4--------------Department ID | People amount in department--------------");
            foreach (var item in IDandAmount)
            {
                Console.WriteLine("{0} | {1}", item.ID, item.Amount);
            }
        }

        /// <summary>
        /// Получите список количества работников в каждом подразделении (номер). Упорядочить по названиям городов, где располагается подразделение.
        /// </summary>
        public static void Linq5()
        {
            var depts = from employee in employees
                        join department in departments on employee.DeptId equals department.Id
                        group department by department.City into dept
                        orderby dept.Key
                        select new { City = dept.Key, Amount = dept.Count() };

            Console.WriteLine("-5-------------- City | People amount in department--------------");
            foreach (var item in depts)
            {
                Console.WriteLine("{0} | {1} ", item.City, item.Amount);
            }
        }

        /// <summary>
        /// Выберите список городов, содержащие букву 'Д' в начале слова.
        /// </summary>
        public static void Linq6()
        {
            /*var cities = from city in departments
                         where  city.City.StartsWith("D")
                         select new { City = city.City };*/

            var cities = departments.Where(city => city.City.StartsWith("D")).Select(city => new { City = city.City });

            Console.WriteLine("-6--------------Cities started with D--------------");
            foreach (var item in cities)
            {
                Console.WriteLine(item.City);
            }
        }

        /// <summary>
        /// Выберите список всех городов, содержащие букву 'л' в середине слова и соответствующее количество работников напротив каждого названия. 
        /// Учесть, что в таблице подразделений может быть город, в котором отсутствуют работники, и в таблице работников могут быть такие, для которых данные поля DptId = NULL.
        /// </summary>
        public static void Linq7()
        {
            var cities =
                (from department in departments
                 join employee in employees on department.Id equals employee.DeptId into lj
                 from rows in lj.DefaultIfEmpty(null)
                 where department.City.IndexOf('l') > 0 && department.City.IndexOf('l') < department.City.Length - 1
                 orderby department.City
                 select new { City = department.City, EmplID = rows == null ? 0 : 1 }).GroupBy(a => a.City).Select(item => new { City = item.Key, Amount = item.Sum(a => a.EmplID) });

            Console.WriteLine("-7-------------- City with 'l' in the name | People amount in department--------------");
            foreach (var item in cities)
            {
                Console.WriteLine("{0} | {1} ", item.City, item.Amount);
            }
        }

        /// <summary>
        /// Выберите список людей, имеющих однофамильцев. Упорядочить по алфавиту.
        /// </summary>
        public static void Linq8()
        {
            var namesGroups = (from name in employees
                               from name2 in employees
                               where name.Id != name2.Id && name.LastName == name2.LastName
                               orderby name.LastName, name.FirstName
                               select new { ID = name.Id, Name = name.FirstName, Surname = name.LastName }).Distinct();

            Console.WriteLine("8---------------Same SurNames--------------");
            foreach (var item in namesGroups)
            {
                Console.WriteLine(item);
            }
        }

        /// <summary>
        /// Выберите список людей, имеющих несколько полных совпадений имени и фамилии из города 'Львов' и упорядочить по именам.
        /// </summary>
        public static void Linq9()
        {
            var namesGroups = ((from name in employees
                                from name2 in employees
                                where name.Id != name2.Id && name.FirstName == name2.FirstName && name.LastName == name2.LastName && name.DeptId == name2.DeptId
                                orderby name.FirstName
                                select new { ID = name.Id, Name = name.FirstName, Surname = name.LastName, DeptID = name.DeptId }).Where(d => d.DeptID == departments.Where(a => a.City == "Lvov").Select(a => new { ID = a.Id }).First().ID)).Distinct();

            Console.WriteLine("-9--------------Same full names--------------");
            foreach (var item in namesGroups)
            {
                Console.WriteLine(item);
            }
        }

        /// <summary>
        /// Получите список городов с количеством работников с именем 'Василий' более 10. Упорядочить по номеру подразделения.
        /// </summary>
        public static void Linq10()
        {
            var depts = (from employee in employees
                         join department in departments on employee.DeptId equals department.Id
                         where employee.FirstName == "Vasiliy"
                         group department by department.City into dept
                         orderby dept.Key
                         select new { City = dept.Key, Amount = dept.Count() }).Where(d => d.Amount > 10);


            Console.WriteLine("-10-------------- City | People amount in department (Vasiliy)--------------");
            foreach (var item in depts)
            {
                Console.WriteLine("{0} | {1} ", item.City, item.Amount);
            }
        }

        public static List<Department> departments = new List<Department>(){  new Department(1,"Dnepropetrovsk"),
                                                                              new Department(2,"Kiev"),
                                                                              new Department(3,"Lvov"),
                                                                              new Department(4,"Donesck"),
                                                                              new Department(5,"Harkov"),
                                                                              new Department(6,"Odessa"),
                                                                              new Department(7,"Hacapetovka"),
                                                                              new Department(8,"Sevastopol"),
                                                                              new Department(9,"Yalta"),
                                                                              new Department(10,"Simferopol"),
                                                                              new Department(11,"Alyshta")};

        public static List<Employee> employees = new List<Employee>() {   new Employee(1,"Dyadka","Dyadkovich",4),
                                                                          new Employee(2,"Tetka","Tetkovna",1),
                                                                          new Employee(3,"Makar","Makarovich",5),
                                                                          new Employee(4,"Alexandr","Alexandrovich",7),
                                                                          new Employee(5,"Andrey","Andreevich",3),
                                                                          new Employee(6,"Andrey","Andreevich",3),
                                                                          new Employee(7,"Vasiliy","sdfg",9),
                                                                          new Employee(8,"Vasiliy","sdf",8),
                                                                          new Employee(9,"Vasiliy","poi",7),
                                                                          new Employee(10,"Vasiliy","wedf",5),
                                                                          new Employee(11,"Vasiliy","mnbv",6),
                                                                          new Employee(12,"Vasiliy","qwerfg",4),
                                                                          new Employee(13,"Vasiliy","edfv",3),
                                                                          new Employee(14,"Vasiliy","asdf",2),
                                                                          new Employee(15,"Vasiliy","yhgb ",1),
                                                                          new Employee(16,"Vasiliy","Alexandrovich",9),
                                                                          new Employee(17,"Vasiliy","zxd",8),
                                                                          new Employee(18,"Vasiliy","kjhb",7),
                                                                          new Employee(19,"Vasiliy","ASFG",7),
                                                                          new Employee(20,"Vasiliy","DGFGH",7),
                                                                          new Employee(21,"Vasiliy","POEW",7),
                                                                          new Employee(22,"Vasiliy","QSSC",7),
                                                                          new Employee(23,"Vasiliy","PLNM",7),
                                                                          new Employee(24,"Vasiliy","FGHRT",7),
                                                                          new Employee(25,"Vasiliy","PLKJHGF",7),
                                                                          new Employee(26,"Vasiliy","QSDFBN",7),
                                                                          new Employee(27,"Vasiliy","QWDSD",7),
                                                                          new Employee(29,"Vasiliy","THJ",8),
                                                                          new Employee(30,"Vasiliy","VBN",8),
                                                                          new Employee(31,"Vasiliy","YJK",8),
                                                                          new Employee(32,"Vasiliy","JKA",8),
                                                                          new Employee(33,"Vasiliy","SFGH",8),
                                                                          new Employee(34,"Vasiliy","LOIUY",8),
                                                                          new Employee(35,"Vasiliy","ASD",8),
                                                                          new Employee(36,"Vasiliy","mnbv",8),
                                                                          new Employee(37,"Vasiliy","XCV ",8),
                                                                          new Employee(38,"Vasiliy","EDX",8),
                                                                          new Employee(39,"Vasiliy","JHGF",8),
                                                                          new Employee(40,"Vasiliy","ZXCT",8),
                                                                          new Employee(41,"Vasiliy","edfv",3)};
    }
}
